ADO.NET Command klasse

Gebruik

Een Command is, in zijn eenvoudigste vorm, een tekenreeks met SQL-instructies die worden doorgegeven aan de database. Een Command kan ook een stored procedure zijn.

De <provider>Command klassen hebben een eigenschap CommandType, waarmee je kan aangeven als de instructie een SQL statement of een call naar een stored procedure is.

Het Command object gebruikt de connectiestring om een verbinding te maken met de SQL server en stuurt een SQL statement of de naam van een stored procedure naar de SQL Server. De SQL server voert het SQL statement of de stored procedure uit en retourneert een of meerdere waarden naar het Command object.

SqlConnection connection = new SqlConnection();
connection.ConnectionString = ConfigurationManager.
    ConnectionStrings["WebwinkelWindowsAuthentication"].ToString();
SqlCommand command = new SqlCommand();
// Het connection object in de command klasse zetten
command.Connection = connection;
// in de CommandText eigenschap stoppen we een SQL statement
// van de stored procedure
string sqlString = "select count(*) from OrderStatus";
command.CommandText = sqlString;
// de connectie openen
connection.Open();
return ((int) command.ExecuteScalar());

Het Command object kan naar de SQL server gestuurd worden op drie manieren. Voor elke manier is er een methode:

Voorbeeld van ExecuteNonQuery

string name = "Bestelling wordt klaargemaakt in ons magazijn";
string description = "Bestelling is geplaatst, betaald en wordt nu klaargemaakt in ons magazijn";
SqlConnection connection = new SqlConnection();
connection.ConnectionString = ConfigurationManager.
    ConnectionStrings["WebwinkelWindowsAuthentication"].ToString();
SqlCommand command = new SqlCommand();
// Het connection object in de command klasse zetten
command.Connection = connection;
// in de CommandText eigenschap stoppen we een SQL statement
// van de stored procedure
string sqlString =
    String.Format("insert into OrderStatus (Name, Description) values ('{0}', '{1}')",
                name, description);
command.CommandText = sqlString;
// de connectie openen
connection.Open();
return ((int)command.ExecuteNonQuery());

Parameters doorgeven in een SQL instructie

Zoals we reeds eerder hebben gezien is een query niets anders dan een string. Je kan dus de string dynamisch opbouwen. Maar dat is niet de goede manier van werken. Dat zet de deur wagenwijd open voor SQL injectie. Iemand kan over het internet je SQL statement zo maar wijzigen e, vervangend door bijvoorbeeld delete from OrderStatus.

In plaats van een string dynamisch op te bouwen, zoals in het slechte voorbeeld hierboven, gebruik je parameters. Alles wat in een parameter wordt doorgegeven wordt beschouwd als kolomgegevens en niet als onderdeel van een SQL statement. Daardoor wordt je code veel veiliger.

Queries met parameters maak je in drie stappen:

1. Construeer de command string van het Command object met behulp van parameters.
2. Declareer een Parameter object en ken waarde toe aan de eigenschappen ervan.
3. Voeg het Parameter object aan de Parameters verzameling van het Command object.

De CommandText string

De eerste stap bestaat er in een command string te maken die parameters bevat die als variabelen gebruikt kunnen worden. De variabelen worden met de criteriawaarden opgevuld wanneer het Command object wordt uitgevoerd. De juiste syntaxis voor een parameter in een SQL query is het gebruik van het “@” teken als prefix voor de naam van het Parameter object:

StringBuilder sql = new StringBuilder();
sql.Append("insert into OrderSatus(");
sql.Append("Name,");
sql.Append("Description");
sql.Append(") ");
sql.Append("values ");
sql.Append("( ");
sql.Append("@Name, ");
sql.Append("@Description");
sql.Append(")");

Een Parameter object declareren

Voor elke parameter die je gebruikt in een SQL CommandText string moet je een Parameter object declareren:

// parameters maken en eigenschappen instellen
SqlParameter pFirstname = new SqlParameter();
pFirstname.ParameterName = "@Name";
pFirstname.DbType = DbType.String;
pFirstname.Size = 255;
pFirstname.Value = "Bestelling wordt klaargemaakt in ons magazijn";

Let erop dat de ParameterName eigenschap van het Parameter object dezelfde naam heeft als de parameter die gebruikt wordt in de SQL command string van het Command object. Daarnaast moet je ook een waarde aan de Value eigenschap van het Parameter object toekennen. Het is namelijk die waarde die tijdens de uitvoering van het Command object aan de parameter, die gebruikt wordt in de SQL CommandText string van het Command object, wordt toegekend.

Toevoegen aan de Parameters collectie

// De parameters toevoegen
command.Parameters.Add(pFirstname);

De hele bedoening is omslachtig. Je kan ook ineens de parameters instellen en aan de collectie toevoegen:

command.Parameters.Add(new SqlParameter("@Name", SqlDbType.Text, 255)).Value = 
         "Bestelling wordt klaargemaakt in ons magazijn";;

Stored procedures

De veiligste manier is het gebruik van stored procedures. Op die manier heb je helemaal geen SQL meer in je C# code maar wordt alle SQL code in de database zelf opgeslagen. Het enige wat in je C# code staat is de naam van de stored procedure die wilt uitvoeren en het invullen van de parameters. De command.CommandType eigenschap stellen we in op CommandType.StoredProcedure om aan te geven dat we geen SQL statement doorsturen maar de naam van een stored procedure.

SqlConnection connection = new SqlConnection();
connection.ConnectionString = ConfigurationManager.
ConnectionStrings["WebwinkelWindowsAuthentication"].ToString();
SqlCommand command = new SqlCommand();
// in de CommandText eigenschap stoppen de naam
// van de stored procedure
string sqlString = "OrderStatusUpdate";
//we use here a getter method to obtain the value to be saved,
command.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar, 255)).Value = 
     "Bestelling wordt klaargemaakt in ons magazijn";
command.Parameters.Add(new SqlParameter("@Description", SqlDbType.NVarChar, 1024)).Value = 
     "Bestelling is geplaatst, betaald en wordt nu klaargemaakt in ons magazijn";
// de id van de te updaten rij
command.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int)).Value = 2;
// zeg aan het command object dat het een stored procedure
// zal krijgen en geen SQL Statement
command.CommandType = CommandType.StoredProcedure;
// stop het sql statement in het command object
command.CommandText = sqlString;
// geeft het connection object door aan het command object
command.Connection = connection;
connection.Open();
//Verbinding geslaagd
command.ExecuteNonQuery();

Commands uitvoeren

Nadat je een<provider>Command object hebt gemaakt, moet je het uitvoeren. Er zijn verschillende manieren om het statement uit te voeren, afhankelijk van wat je wenst terug te krijgen van de database. De <provider>Command klassen bieden de volgende uitvoeringsmethoden:

➤ ExecuteNonQuery — voert de opdracht uit maar retourneert geen typed IDataReader wel het aantal rijen dat bewerkt zijn geworden.

➤ ExecuteReader — voert de opdracht uit en retourneert een typed IDataReader

➤ ExecuteScalar — voert de opdracht uit en retourneert e waarde van de eerste kolom in de eerste rij

Daarnaast heb je nog:

➤ ExecuteXmlReader — voert de instructie uit en retourneert een XmlReader object